﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;

namespace Models
{
    public class XmlImportDataAccess : DataAccessBase
    {

        #region Properties

        #endregion

        #region Creation

        public XmlImportDataAccess()
        {

        }

        #endregion

        #region Indsæt logik, for Brands, Categories, Sizes og Colors

        //indsæt brand i databasen, denne metode styrer ikke connections selv, da det er bulk inserts
        public void InsertBrand(int id, string name)
        {
            command.Parameters.Clear();
            command.CommandText = "SP_InsertBrand";
            command.Parameters.AddWithValue("@Id", id);
            command.Parameters.AddWithValue("@Name", name);
            command.ExecuteNonQuery();
        }

        //indsæt category i database, bulk
        public void InsertCategory(int id, string name)
        {
            command.Parameters.Clear();
            command.CommandText = "SP_InsertCategory";
            command.Parameters.AddWithValue("@Id", id);
            command.Parameters.AddWithValue("@Name", name);
            command.ExecuteNonQuery();
        }

        //indsæt size i database, bulk
        public void InsertSize(int id, string name)
        {
            command.Parameters.Clear();
            command.CommandText = "SP_InsertSize";
            command.Parameters.AddWithValue("@Id", id);
            command.Parameters.AddWithValue("@Size", name);
            command.ExecuteNonQuery();
        }

        //indsæt color i database, bulk
        public void InsertColor(int id, string name)
        {
            command.Parameters.Clear();
            command.CommandText = "SP_InsertColor";
            command.Parameters.AddWithValue("@Id", id);
            command.Parameters.AddWithValue("@Color", name);
            command.ExecuteNonQuery();
        }

        #endregion

        #region Generic Items

        public Dictionary<int, DateTime> GetDateTimeFromGenericItems()
        {
            var dict = new Dictionary<int, DateTime>();

            command.Parameters.Clear();
            command.CommandText = "SP_GetDateTimeFromGenericItems";

            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    dict.Add((int)reader["Id"], (DateTime)reader["Modified"]);
                }
            }
            return dict;
        }

        public void UpdateGenericItem(int id, string name, decimal price, DateTime modified)
        {
            command.Parameters.Clear();
            command.CommandText = "SP_UpdateGenericItemById";
            command.Parameters.AddWithValue("@Id", id);
            command.Parameters.AddWithValue("@Name", name);
            command.Parameters.AddWithValue("@Price", price);
            command.Parameters.AddWithValue("@Modified", modified);
            command.ExecuteNonQuery();
        }

        public List<int> AllGenericItemIds()
        {
            var list = new List<int>();
            command.Parameters.Clear();
            command.CommandText = "SP_GetAllGenericItemIds";

            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    list.Add((int)reader["Id"]);
                }
            }
            return list;
        }

        public void MarkGenericItemAsInactive(int id)
        {
            command.Parameters.Clear();
            command.CommandText = "SP_MarkGenericItemAsInactive";
            command.Parameters.AddWithValue("@Id", id);
            command.ExecuteNonQuery();
        }

        public int CountGenericItem(int id)
        {
            command.Parameters.Clear();
            command.CommandText = "SP_GetGenericItemCount";
            command.Parameters.AddWithValue("@Id", id);
            return (int)command.ExecuteScalar();
        }

        public void InsertGenericItem(int id, string name, int brandId, int categoryId, decimal price, DateTime modified)
        {
            command.Parameters.Clear();
            command.CommandText = "SP_InsertGenericItem";
            command.Parameters.AddWithValue("@Id", id);
            command.Parameters.AddWithValue("@Name", name);
            command.Parameters.AddWithValue("@BrandId", brandId);
            command.Parameters.AddWithValue("@CategoryId", categoryId);
            command.Parameters.AddWithValue("@Price", price);
            command.Parameters.AddWithValue("@Modified", modified);
            command.Parameters.AddWithValue("@Available", 1);
        }

        public void BulkInsertGenericItems(DataTable table)
        {
            using (var sqlConnection = base.NewConnection())
            using (var copy = new SqlBulkCopy(sqlConnection))
            {
                sqlConnection.Open();
                copy.DestinationTableName = "GenericItem";
                copy.WriteToServer(table);
            }
        }

        public void BulkInsertGenericItemDescriptions(DataTable table)
        {
            using (var sqlConnection = base.NewConnection())
            using (var copy = new SqlBulkCopy(sqlConnection))
            {
                sqlConnection.Open();
                copy.DestinationTableName = "ItemDescription";
                copy.WriteToServer(table);
            }
        }

        #endregion

        #region Items

        public int CountItem(int id)
        {
            command.Parameters.Clear();
            command.CommandText = "SP_GetItemCount";
            command.Parameters.AddWithValue("@Id", id);
            return (int)command.ExecuteScalar();
        }

        public void InsertItem(int id, int colorId, int sizeId, int genericItemId, decimal quantity, DateTime modified)
        {
            command.Parameters.Clear();
            command.CommandText = "SP_InsertItem";
            command.Parameters.AddWithValue("@Id", id);
            command.Parameters.AddWithValue("@ColorId", colorId);
            command.Parameters.AddWithValue("@SizeId", sizeId);
            command.Parameters.AddWithValue("@GenericItemId", genericItemId);
            command.Parameters.AddWithValue("@Quantity", quantity);
            command.Parameters.AddWithValue("@Modified", modified);
            command.Parameters.AddWithValue("@Available", 1);
            command.ExecuteScalar();
        }

        public List<int> AllItemIds()
        {
            var list = new List<int>();
            command.Parameters.Clear();
            command.CommandText = "SP_GetAllItemIds";

            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    list.Add((int)reader["Id"]);
                }
            }
            return list;
        }

        public void MarkItemAsInactive(int id)
        {
            command.Parameters.Clear();
            command.CommandText = "SP_MarkItemAsInactive";
            command.Parameters.AddWithValue("@Id", id);
            command.ExecuteNonQuery();
        }

        public Dictionary<int, DateTime> GetDateTimeFromItems()
        {
            var dict = new Dictionary<int, DateTime>();

            command.Parameters.Clear();
            command.CommandText = "SP_GetDateTimeFromItems";

            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    dict.Add((int)reader["Id"], (DateTime)reader["Modified"]);
                }
            }
            return dict;
        }

        public void UpdateItem(int id, int quantity, DateTime modified)
        {
            command.Parameters.Clear();
            command.CommandText = "SP_UpdateItemById";
            command.Parameters.AddWithValue("@Id", id);
            command.Parameters.AddWithValue("@Quantity", quantity);
            command.Parameters.AddWithValue("@Modified", modified);
            command.ExecuteNonQuery();
        }

        public void BulkInsertItems(DataTable table)
        {
            using (var connection = base.NewConnection())
            using (var copy = new SqlBulkCopy(connection))
            {
                connection.Open();
                copy.DestinationTableName = "Item";
                copy.WriteToServer(table);
            }
        }

        #endregion
    }
}
